package ext.tianma.part.util;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.rmi.RemoteException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONException;

import ext.tianma.MaterialClassification.CreatePartClassificationUtil;
import ext.tianma.bom.eco.BOMHelp;
import ext.tianma.bom.util.ClassificationUtil;
import ext.tianma.bom.util.QueryUtil;
import ext.tianma.util.IbaAttributeUtil;
import wt.csm.navigation.ClassificationNode;
import wt.doc.WTDocument;
import wt.fc.ObjectIdentifier;
import wt.fc.ObjectReference;
import wt.fc.PersistenceHelper;
import wt.fc.QueryResult;
import wt.iba.definition.ReferenceDefinition;
import wt.iba.value.IBAHolder;
import wt.iba.value.IBAReferenceable;
import wt.iba.value.ReferenceValue;
import wt.lifecycle.LifeCycleHelper;
import wt.lifecycle.State;
import wt.part.WTPart;
import wt.part.WTPartMaster;
import wt.part.WTPartStandardConfigSpec;
import wt.query.KeywordExpression;
import wt.query.QuerySpec;
import wt.query.SearchCondition;
import wt.type.ClientTypedUtility;
import wt.type.TypeDefinitionForeignKey;
import wt.type.TypeDefinitionReference;
import wt.type.Typed;
import wt.util.WTException;
import wt.util.WTPropertyVetoException;
import wt.vc.config.ConfigHelper;
import wt.vc.views.View;
import wt.vc.views.ViewException;
import wt.vc.views.ViewHelper;
import wt.vc.views.ViewManageable;

public class countPartAndDocument {
	public static String STATUTE_PATH = "/ptc/Windchill/codebase/ext/tianma/cfg/exportParts.xlsx";
	public static String PART_STATUTE_PATH = "/ptc/Windchill/codebase/ext/tianma/part/config/partState.xlsx";
	private final static String PATH = "/ext/tianma/util/queryClassificationPath.xml";
	public static String softtype_finishgood = "cn.tianma.FinishedGood";// 成品
	public static String softtype_semiFinishgood = "cn.tianma.SemiFinishedGood";// 半成品
	public static String softtype_semiFinishgood_C4 = "cn.tianma.C4";// 半成品——C4
	public static String softtype_semiFinishgood_CF = "cn.tianma.CF";// 半成品——CF
	public static String softtype_semiFinishgood_XP = "cn.tianma.XP";// 半成品——XP
	public static String softtype_semiFinishgood_SP = "cn.tianma.SP";// 半成品——SP
	public static String softtype_RawMaterial = "cn.tianma.RawMaterial";// 上偏光片
	public static String softtype_RawMaterial_TopPolarizer = "cn.tianma.TopPolarizer";// 原材料
	public static final String ProcessDocuments = "cn.tianma.ProcessDocuments";//工艺文件
	public static final String AcknowDocuments = "cn.tianma.Acknowledgment";
	public static final String PDFDocuments = "cn.tianma.ControlDocument";//PDF文件
	public static final String ProjecDocuments = "cn.tianma.ProjectManagementDoc";//PDF文件
	public static String softtype_RawMaterial_BotPolarizerl = "wt.part.WTPart|cn.tianma.RawMaterial|cn.tianma.Polarizer|cn.tianma.BotPolarizerl";// 下偏光片
	public static String softtype_PhantomPolarizer = "cn.tianma.PhantomPolarizer";// 虚拟偏光片组件

	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";
	private static final int[] DEFAULT_CONDITION_ARRAY = new int[] { 0 };

	/***
	 * 获取系统中所有成品数据 gx
	 * 
	 * @throws ViewException
	 * @throws WTException
	 * @throws RemoteException 
	 */
	public static void ExportExportPartByIBAValues() throws ViewException, WTException, RemoteException {
		System.out.println("===>>start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_semiFinishgood);
			// IBA 属性过滤
			Timestamp date = Timestamp.valueOf("2019-9-15 00:00:00");	
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			SearchCondition sc7 = new SearchCondition(WTPart.class, "thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL, date);
			qs.appendAnd();
			qs.appendSearchCondition(sc7);
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	
	public static void ExportExportPartByIBAValuesC4() throws ViewException, WTException, RemoteException {
		System.out.println("===>>start C4=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_semiFinishgood_C4);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	public static void ExportExportPartByIBAValuesCF() throws ViewException, WTException, RemoteException {
		System.out.println("===>>CF start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_semiFinishgood_CF);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	public static void ExportExportPartByIBAValuesXP() throws ViewException, WTException, RemoteException {
		System.out.println("===>> XP start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_semiFinishgood_XP);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	public static void ExportExportPartByIBAValuesSP() throws ViewException, WTException, RemoteException {
		System.out.println("===>>SP start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_semiFinishgood_SP);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	public static void ExportExportPartByIBAValuesRA() throws ViewException, WTException, RemoteException {
		System.out.println("===>>原材料 start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_RawMaterial);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	public static void ExportExportPartByIBAValuesTOP() throws ViewException, WTException, RemoteException {
		System.out.println("===>>上偏光片 start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_RawMaterial_TopPolarizer);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	public static void ExportExportPartByIBAValuesBOP() throws ViewException, WTException, RemoteException {
		System.out.println("===>>下偏光片 start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_RawMaterial_BotPolarizerl);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	
	
	public static void ExportDocumentByIBAValues() throws ViewException, WTException, RemoteException {
		System.out.println("===>> document start=====>>");
			QuerySpec qs = new QuerySpec(WTDocument.class);
			qs.setAdvancedQueryEnabled(true);
			TypeDefinitionReference clientType = ClientTypedUtility.getTypeDefinitionReference(ProjecDocuments);//查询工艺文件
			SearchCondition scOther = new SearchCondition(WTDocument.class,
					Typed.TYPE_DEFINITION_REFERENCE + "." + TypeDefinitionReference.KEY + "."
							+ TypeDefinitionForeignKey.BRANCH_ID,
					SearchCondition.EQUAL, clientType.getKey().getBranchId());
			qs.appendWhere(scOther,new int[]{0});
			// IBA 属性过滤
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTDocument.class, WTDocument.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	
	public static void softtype_PhantomPolarizer() throws ViewException, WTException, RemoteException {
		System.out.println("===>>虚拟偏光片组件 start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_PhantomPolarizer);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}
	
	
	
	
	
	
	
	
	
	/***
	 * 获取系统中所有成品数据 gx
	 * 
	 * @throws ViewException
	 * @throws WTException
	 * @throws RemoteException 
	 */
	public static void ExportPartByIBAValues() throws ViewException, WTException, RemoteException {
		System.out.println("===>>start=====>>");
	
			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_semiFinishgood);
			// IBA 属性过滤
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			qs.appendAnd();
			qs.appendWhere(
					new SearchCondition(WTPart.class, WTPart.LIFE_CYCLE_STATE, SearchCondition.EQUAL, "NewProduct"));//新品
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
		
		System.out.println("======end  "+qr.size()+"条====>>>");
	}

}
